/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package lomtools.persistence;

import econutri.persistencia.Config;
import java.io.IOException;
import java.lang.annotation.Annotation;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Leonardo Oliveira Moreira
 * 
 */
public class GenericDAO<E extends Bean> {

    private static String DATABASE_URL = Config.getProperty("database.url");
    private static String DATABASE_USERNAME = Config.getProperty("database.username");
    private static String DATABASE_PASSWORD = Config.getProperty("database.password");
    private static String DATABASE_DRIVER = Config.getProperty("database.driver");
    
    private Connection connection;

    private static final String getProperty(String key) {
        if (key == null || key.trim().length() == 0) {
            return "";
        }
        String result = "";
        Properties properties = new Properties();
        try {
            properties.load(GenericDAO.class.getResourceAsStream("database.properties"));
            result = properties.getProperty(key);
            if (result == null || result.trim().length() == 0) {
                return "";
            }
        } catch (IOException ex) {
        }
        return result;
    }

    private Bean getBean(ResultSet rs, E obj) throws SQLException {
        ResultSetMetaData rsmd = rs.getMetaData();
        Bean bean = null;
        try {
            bean = (Bean) obj.getClass().newInstance();
            Method[] methods = bean.getClass().getMethods();
            for (int i = 1; rsmd != null && i <= rsmd.getColumnCount(); i++) {
                String columnName = rsmd.getColumnName(i);
                String attributeName = BeanUtils.getAttributeName(bean, columnName);
                if (attributeName != null) {
                    for (Method m : methods) {
                        if (m.getName().contains("set" + attributeName.substring(0, 1).toUpperCase() + attributeName.substring(1)) && m.getParameterTypes().length == 1) {
                            try {
                                m.setAccessible(true);
                                String columnValue = rs.getString(columnName);
                                if (m.getParameterTypes()[0].getName().equals("java.lang.Integer")) {
                                    if (columnValue != null)
                                        m.invoke(bean, Integer.parseInt(columnValue));
                                    break;
                                }
                                if (m.getParameterTypes()[0].getName().equals("java.lang.Double")) {
                                    if (columnValue != null)
                                        m.invoke(bean, Double.parseDouble(columnValue));
                                    break;
                                }
                                if (m.getParameterTypes()[0].getName().equals("java.lang.Boolean")) {
                                    if (columnValue != null)
                                        m.invoke(bean, Boolean.parseBoolean(columnValue));
                                    break;
                                }
                                if (m.getParameterTypes()[0].getName().equals("java.lang.Float")) {
                                    if (columnValue != null)
                                        m.invoke(bean, Float.parseFloat(columnValue));
                                    break;
                                }
                                if (m.getParameterTypes()[0].getName().equals("java.lang.Long")) {
                                    if (columnValue != null)
                                        m.invoke(bean, Long.parseLong(columnValue));
                                    break;
                                }
                                if (m.getParameterTypes()[0].getName().equals("java.util.Date")) {
                                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                                    try {
                                        if (columnValue != null)
                                            m.invoke(bean, sdf.parse(columnValue));
                                    } catch (ParseException ex) {
                                        Logger.getLogger(GenericDAO.class.getName()).log(Level.SEVERE, null, ex);
                                    }
                                    break;
                                }
                                if (columnValue != null)
                                    m.invoke(bean, String.valueOf(columnValue));
                            } catch (IllegalAccessException ex) {
                                throw new SQLException(ex);
                            } catch (IllegalArgumentException ex) {
                                throw new SQLException(ex);
                            } catch (InvocationTargetException ex) {
                                throw new SQLException(ex);
                            }
                        }
                    }
                }
            }
            return bean;
        } catch (InstantiationException ex) {
            throw new SQLException(ex);
        } catch (IllegalAccessException ex) {
            throw new SQLException(ex);
        }
    }

    public GenericDAO() throws SQLException {
        try {
            Class.forName(DATABASE_DRIVER);
            connection = DriverManager.getConnection(DATABASE_URL, DATABASE_USERNAME, DATABASE_PASSWORD);
            connection.setAutoCommit(false);
        } catch (SQLException ex) {
            throw ex;
        } catch (ClassNotFoundException ex) {
            throw new SQLException(ex);
        }
    }

    @Override
    protected void finalize() throws Throwable {
        if (connection != null && !connection.isClosed()) {
            connection.close();
            connection = null;
        }
        super.finalize();
    }

    public ResultSet executeQuery(String sql) throws SQLException {
        try {
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery(sql);
            connection.commit();
            return resultSet;
        } catch (SQLException ex) {
            connection.rollback();
            throw ex;
        }
    }

    public int delete(String sql) throws SQLException {
        int result = 0;
        try {
            Statement statement = connection.createStatement();
            result = statement.executeUpdate(sql);
            connection.commit();
        } catch (SQLException ex) {
            connection.rollback();
            throw ex;
        }
        return result;
    }

    public int update(String sql) throws SQLException {
        int result = 0;
        try {
            Statement statement = connection.createStatement();
            result = statement.executeUpdate(sql);
            connection.commit();
        } catch (SQLException ex) {
            connection.rollback();
            throw ex;
        }
        return result;
    }

    public boolean insert(String sql) throws SQLException {
        try {
            Statement statement = connection.createStatement();
            statement.execute(sql);
            connection.commit();
            return true;
        } catch (SQLException ex) {
            connection.rollback();
            throw ex;
        }
    }

    public void close() {
        try {
            connection.close();
        } catch (SQLException ex) {
            
        }
    }

    public E save(E newInstance) throws SQLException {
        if (newInstance == null)
            throw new SQLException("O objeto esta nulo");
        // Tratamento do Identificador
        Field[] declaredFields = newInstance.getClass().getDeclaredFields();
        for (Field f : declaredFields) {
            boolean fieldId = false;
            String sequenceGenerator = null;
            String sequenceName = null;
            boolean canExecuteSequence = false;
            String sequenceValue = null;
            Annotation[] declaredAnnotations = f.getDeclaredAnnotations();
            for (Annotation a : declaredAnnotations) {
                if (a.annotationType() == javax.persistence.Id.class) {
                    fieldId = true;
                }
                if (a.annotationType() == javax.persistence.SequenceGenerator.class) {
                    javax.persistence.SequenceGenerator sg = (javax.persistence.SequenceGenerator) a;
                    sequenceName = sg.sequenceName();
                    sequenceGenerator = sg.name();
                }
                if (a.annotationType() == javax.persistence.GeneratedValue.class) {
                    javax.persistence.GeneratedValue gv = (javax.persistence.GeneratedValue) a;
                    canExecuteSequence = (gv.generator().equals(sequenceGenerator));
                }
                if (a.annotationType() == javax.persistence.Column.class) {
                    javax.persistence.Column c = (javax.persistence.Column) a;
                    if (fieldId) {
                        Object value = DAOUtils.getValue(newInstance, f);
                        if (value == null) {
                            if (canExecuteSequence) {
                                String sqlSequence = DAOUtils.getSelectSequence(sequenceName);
                                ResultSet rs = executeQuery(sqlSequence);
                                while (rs != null && rs.next()) {
                                    sequenceValue = rs.getString("sequence");
                                }
                                try {
                                    f.setAccessible(true);
                                    if (sequenceValue != null && f.getType() == java.lang.Integer.class) {
                                        f.set(newInstance, Integer.parseInt(sequenceValue));
                                    }
                                    if (sequenceValue != null && f.getType() == java.lang.Double.class) {
                                        f.set(newInstance, Double.parseDouble(sequenceValue));
                                    }
                                    if (sequenceValue != null && f.getType() == java.lang.Long.class) {
                                        f.set(newInstance, Long.parseLong(sequenceValue));
                                    }
                                    if (sequenceValue != null && f.getType() == java.lang.Boolean.class) {
                                        f.set(newInstance, Boolean.parseBoolean(sequenceValue));
                                    }
                                    if (sequenceValue != null && f.getType() == java.lang.Float.class) {
                                        f.set(newInstance, Float.parseFloat(sequenceValue));
                                    }
                                    if (sequenceValue != null && f.getType() == java.util.Date.class) {
                                        SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy");
                                        try {
                                            f.set(newInstance, sdf.parse(sequenceValue));
                                        } catch (ParseException ex) {
                                            f.set(newInstance, null);
                                        }
                                    }
                                    if (sequenceValue != null && f.getType() == java.lang.String.class) {
                                        f.set(newInstance, sequenceValue);
                                    }
                                } catch (IllegalArgumentException ex) {
                                    Logger.getLogger(GenericDAO.class.getName()).log(Level.SEVERE, null, ex);
                                } catch (IllegalAccessException ex) {
                                    Logger.getLogger(GenericDAO.class.getName()).log(Level.SEVERE, null, ex);
                                }
                            }
                            else {
                                throw new SQLException("Nenhum identificador foi encontrado");
                            }
                        }
                    }
                }
            }
        }
        // ---
        String sql = DAOUtils.getInsert(newInstance);
        System.out.println("### SQL: " + sql);
        boolean result = insert(sql);
        if (! result)
            throw new SQLException("Nenhum registro inserido");
        return findById(newInstance);
    }

    public E update(E transientObject) throws SQLException {
        if (transientObject == null)
            throw new SQLException("O objeto esta nulo");
        String sql = DAOUtils.getUpdate(transientObject);
        System.out.println("### SQL: " + sql);
        int result = update(sql);
        if (result == 0)
            throw new SQLException("Nenhum registro removido");
        else
            if (result > 1)
                throw new SQLException("Mais de um registro removido");
        return findById(transientObject);
    }

    public void delete(E persistentObject) throws SQLException {
        if (persistentObject == null)
            throw new SQLException("O objeto esta nulo");
        persistentObject = findById(persistentObject);
        if (persistentObject == null)
            throw new SQLException("O objeto esta nulo");
        String sql = DAOUtils.getDelete(persistentObject);
        System.out.println("### SQL: " + sql);
        int result = delete(sql);
        if (result == 0)
            throw new SQLException("Nenhum registro removido");
        else
            if (result > 1)
                throw new SQLException("Mais de um registro removido");

    }

    public E findById(E obj) throws SQLException {
        if (obj == null)
            throw new SQLException("O objeto esta nulo");
        String sql = DAOUtils.getSelectId(obj);
        System.out.println("### SQL: " + sql);
        ResultSet rs = executeQuery(sql);
        while (rs != null && rs.next()) {
            return (E) getBean(rs, obj);
        }
        return null;
    }

    public List<E> findAll(E obj) throws SQLException {
        if (obj == null)
            throw new SQLException("O objeto esta nulo");
        List<E> resultList = new ArrayList<E>();
        String sql = DAOUtils.getSelect(obj);
        System.out.println("### SQL findAll: " + sql);
        ResultSet rs = executeQuery(sql);
        while (rs != null && rs.next()) {
            try {
                resultList.add((E) getBean(rs, obj));
            } catch (SQLException ex) {
                throw ex;
            }
        }
        return resultList;
    }
    
}
